# -*- coding: utf-8 -*-
# vim: set noet ts=4:
#
# scim-python
#
# Copyright (c) 2008-2008 Yu Yuwei <acevery@gmail.com>
#
#
# This library is free software; you can redistribute it and/or
# modify it under the terms of the GNU Lesser General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This library is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public
# License along with this program; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place, Suite 330,
# Boston, MA  02111-1307  USA
#
# $Id: $
#

import os
import os.path as path
import sqlite3
import XMDict
import uuid
import time
import re

patt_r = re.compile(r'c([ea])(\d):(.*)')
patt_p = re.compile(r'p(-{0,1}\d)(\d)')

# first make some number index we will used :)
#(MLEN, CLEN, M0, M1, M2, M3, M4, PHRASE, FREQ, USER_FREQ) = range (0,10)


class XMSQLiteDB:
	'''Phrase database for XingMa'''
	def __init__(self, name = 'xm.db', user_db = None, filename = None ):
		# first we use the Parse in XMDict, which transform the char(a,b,c,...) to int(1,2,3,...) to fasten the sql enquiry
		self.parse = XMDict.Parse
		self.deparse = XMDict.Deparse
		
		if filename:
			self.db = sqlite3.connect( filename )
			return

		# open system phrase db
		self.db = sqlite3.connect( name )
		try:
			self.db.execute( 'PRAGMA page_size = 8192; ' )
			self.db.execute( 'PRAGMA cache_size = 5000; ' )
			# increase the cache size to speedup sqlite enquiry
			self.db.execute( 'PRAGMA temp_store = MEMORY; ' )
			self.db.execute( 'PRAGMA synchronous = OFF; ' )
		except:
			pass
		# create IME property table
		sqlstr = 'CREATE TABLE IF NOT EXISTS main.ime (attr TEXT, val TEXT);' 
		self.db.executescript( sqlstr )
		# make sure we have values in ime table.
		if not self.db.execute('SELECT * FROM main.ime;').fetchall():
			ime_keys={'name':'',
					  'name.zh_cn':'',
					  'name.zh_hk':'',
					  'name.zh_tw':'',
					  'author':'somebody', 
					  'uuid':'%s' % uuid.uuid4(),
					  'serial_number':'%s' % time.strftime('%Y%m%d'),
					  'icon':'/usr/share/scim/icons/scim-python.png',
					  'credit':'GPL',
					  'languages':'zh',
					  'valid_input_chars':'abcdefghijklmnopqrstuvwxyz',
					  'max_key_length':'4',
			#		  'commit_keys':'space',
			#		  'forward_keys':'Return',
			#		  'select_keys':'1,2,3,4,5,6,7,8,9,0',
			#		  'page_up_keys':'Page_Up,minus',
			#		  'page_down_keys':'Page_Down,equal',
					  'status_prompt':'CN',
					  'def_full_width_punct':'TRUE',
					  'def_full_width_letter':'FALSE',
					  'user_can_define_phrase':'FALSE',
					  'pinyin_mode':'FALSE',
					  'rules':''}
					  #'rules':'ce2:p11+p12+p21+p22;ce3:p11+p21+p22+p31;ca4:p11+p21+p31+p41'}
			# inital the attribute in ime table, which should be updated from mabiao
			for _name in ime_keys:
					sqlstr = 'INSERT INTO main.ime (attr,val) VALUES (?,?);'
					self.db.execute( sqlstr, (_name,ime_keys[_name]) )
		# share variables in this class:
		self._mlen = int ( self.get_ime_property ("max_key_length") )
		#(MLEN, CLEN, M0, M1, M2, M3, M4, PHRASE, FREQ, USER_FREQ) = range (0,10)
		self._pt_index = ['mlen','clen']
		for i in range(self._mlen):
			self._pt_index.append ('m%d' %i)
		self._pt_index += ['phrase','freq','user_freq']
		self.user_can_define_phrase = self.get_ime_property('user_can_define_phrase')
		if self.user_can_define_phrase:
			if self.user_can_define_phrase.lower() == u'true' :
				self.user_can_define_phrase = True
			else:
				self.user_can_define_phrase = False
		else:
			print 'Could not find "user_can_define_phrase" entry from database, is it a outdated database?'
			self.user_can_define_phrase = False
		self.rules = self.get_rules ()

		
		# user database:
		if user_db != None:
			home_path = os.getenv ("HOME")
			xingma_path = path.join (home_path, ".scim", "scim-python", "xingma")
			user_db = path.join (xingma_path, user_db)
			if not path.isdir (xingma_path):
				os.makedirs (xingma_path)
			try:
				desc = self.get_database_desc (user_db)
				if desc == None :
					self.init_user_db (user_db)
				elif desc["version"] != "0.1":
					new_name = "%s.%d" %(user_db, os.getpid())
					print >> sys.stderr, "Can not support the user db. We will rename it to %s" % new_name
					os.rename (user_db, new_name)
					self.init_user_db (user_db)
			except:
				import traceback
				traceback.print_exc()
		else:
			user_db = ":memory:"
		
		# open user phrase database
		try:
			self.db.execute ('ATTACH DATABASE "%s" AS user_db;' % user_db)
		except:
			print >> sys.stderr, "The user database was damaged. We will recreate it!"
			os.rename (user_db, "%s.%d" % (user_db, os.getpid ()))
			self.init_user_db (user_db)
			self.db.execute ('ATTACH DATABASE "%s" AS user_db;' % user_db)

		
		# try create all tables in user database
		self.create_tables ("user_db")
		self.create_indexes ("user_db")
		self.generate_userdb_desc ()

	def create_tables (self, database):
		'''Create tables that contain all phrase'''

		try:
			self.db.execute( 'PRAGMA default_cache_size = 5000; ' )
			# increase the cache size to speedup sqlite enquiry
		except:
			pass
		if database == 'main':
			# create  ikeys table
			sqlstr = 'CREATE TABLE IF NOT EXISTS %s.ikeys (ikey TEXT PRIMARY KEY, id INTEGER);' % database
			self.db.execute ( sqlstr )
		
			# create goucima table, this table is used in construct new phrases
			sqlstr = 'CREATE TABLE IF NOT EXISTS %s.goucima (zi TEXT PRIMARY KEY' % database
			for i in range(self._mlen):
				sqlstr += ', g%d INTEGER' % i 
			sqlstr += ');'
			self.db.execute ( sqlstr )

			# create pinyin table, this table is used in search single character for user handly
			sqlstr = 'CREATE TABLE IF NOT EXISTS %s.pinyin ( plen INTEGER, ' % database
			for i in range(6):
				sqlstr += 'p%d INTEGER, ' % i 
			sqlstr += 'zi TEXT, freq INTEGER);'
			self.db.execute ( sqlstr )

		# create phrase table (mabiao)
		sqlstr = 'CREATE TABLE IF NOT EXISTS %s.phrases ( mlen INTEGER, clen INTEGER, ' % database
		for i in range(self._mlen):
			sqlstr += 'm%d INTEGER, ' % i 
		sqlstr += 'phrase TEXT, freq INTEGER, user_freq INTEGER);'
		self.db.execute ( sqlstr )
		self.db.commit ()
	
	def update_ime (self, attrs):
		'''Update attributes in ime table, attrs is a iterable object
		Like [(attr,val), (attr,val), ...]
		'''
		sqlstr = 'UPDATE main.ime SET val = ? WHERE attr = ?;' 
		for attr,val in attrs:
			_sqlstr = 'SELECT * from main.ime WHERE attr = ?' 
			res = self.db.execute( _sqlstr, (attr,) ).fetchall()
			if res:
				self.db.execute(sqlstr,(val,attr))
			else:
				#print '"',attr,'"'," didn't in ime property now!"
				pass
		# we need to update some self variables now.
		self._mlen = int (self.get_ime_property ('max_key_length' ))
		self._pt_index = ['mlen','clen']
		for i in range(self._mlen):
			self._pt_index.append ('m%d' %i)
		self._pt_index += ['phrase','freq','user_freq']
		self.user_can_define_phrase = self.get_ime_property('user_can_define_phrase')
		if self.user_can_define_phrase:
			if self.user_can_define_phrase.lower() == u'true' :
				self.user_can_define_phrase = True
			else:
				self.user_can_define_phrase = False
		else:
			print 'Could not find "user_can_define_phrase" entry from database, is it a outdated database?'
			self.user_can_define_phrase = False
		self.rules = self.get_rules ()

		self.db.commit()

	def get_rules (self):
		'''Get phrase construct rules'''
		rules={'above':4}
		if self.user_can_define_phrase:
			try:
				_rules = self.get_ime_property ('rules')
				if _rules:
					_rules = _rules.strip().split(';')
				for rule in _rules:
					res = patt_r.match (rule)
					if res:
						cms = []
						if res.group(1) == 'a':
							rules['above'] = int(res.group(2))
						_cms = res.group(3).split('+')
						if len(_cms) > int(self.get_ime_property('max_key_length')):
							print 'rule: "%s" over max key length' % rule
							break
						for _cm in _cms:
							cm_res = patt_p.match(_cm)
							cms.append(( int(cm_res.group(1)),int(cm_res.group(2)) ))
						rules[int(res.group(2))]=cms
					else:
						print 'not a legal rule: "%s"'  % rule 
			except Exception:
				import traceback
				traceback.print_exc ()
			return rules
		else:
			return ""

	def add_phrases (self, phrases, database = 'main'):
		'''Add phrases to database, phrases is a iterable object
		Like: [(xingma, phrase, freq ,user_freq), (xingma, phrase, freq, user_freq), ...]
		'''
		sqlstr = 'INSERT INTO %s.phrases ( mlen, clen, '
		sql_suffix = 'VALUES ( ?, ?, '
		for i in range(self._mlen):
			sqlstr += 'm%d, ' % i
			sql_suffix += '?, '
		sqlstr += 'phrase, freq, user_freq) '
		sql_suffix += '?, ?, ? );'
		sqlstr += sql_suffix
		
		count = 1
		for xingma,phrase,freq,user_freq in phrases:
			try:
				xm = self.parse(xingma)
				if len(xm) != len(xingma):
					print 'In %s %s: we parse xingma fail' % (phrase, xingma )
					break
				record = [None, None, None, None, None]
				for i in range(self._mlen):
					record.append(None)
				record [0] = len (xingma)
				record [1] = len (phrase)
				for i in range(0,len(xingma)):
					record [ 2+i ] = xm[i].get_key_id()
				record [2+self._mlen] = phrase
				record [2+self._mlen+1] = freq
				record [2+self._mlen+2] = user_freq
				self.db.execute (sqlstr % database, record)
			except Exception:
				import traceback
				traceback.print_exc()
			count += 1

		self.db.commit()	

	def add_goucima (self, gcms):
		'''Add goucima into database, gcms is iterable object
		Like gcms = [(zi,goucima),(zi,goucima), ...]
		'''
		count = 1
		for zi,gcm in gcms:
			_con = ''
			_val = ''
			_len = min ( len(gcm),self._mlen)
			for i in range( _len ):
				_con += ', g%d' % i
				_val += ', ?' 
			sqlstr = '''INSERT INTO main.goucima ( zi %s )
			VALUES ( ? %s );''' % (_con, _val)
			try:
				gc = self.parse(gcm)
				if len(gc) != len(gcm):
					error_m = u'%s %s: Can not parse goucima' % (zi, gcm )
					raise Exception ( error_m.encode ('utf8') )
				record = [zi]
				for i in range(_len):
					record.append( gc[i].get_key_id())
				self.db.execute (sqlstr , record)
			
			except Exception:
				import traceback
				traceback.print_exc()
			count += 1
		self.db.commit()

	def add_pinyin (self, pinyins, database = 'main'):
		'''Add pinyin to database, pinyins is a iterable object
		Like: [(zi,pinyin, freq), (zi, pinyin, freq), ...]
		'''
		sqlstr = 'INSERT INTO %s.pinyin ( plen, '
		sql_suffix = 'VALUES ( ?, '
		for i in range(6):
			sqlstr += 'p%d, ' % i
			sql_suffix += '?, '
		sqlstr += 'zi, freq ) '
		sql_suffix += '?, ? );'
		sqlstr += sql_suffix
		
		count = 1
		for pinyin,zi,freq in pinyins:
			try:
				py = self.parse(pinyin)
				if len(py) != len(pinyin):
					error_m = u'%s %s: Can not parse pinyin' % (zi, pinyin )
					raise Exception ( error_m.encode ('utf8') )
				record = [None, None, None, None, None, None, None, None, None]
				record [0] = len (pinyin)
				for i in range(0,len(pinyin)):
					record [ 1+i ] = py[i].get_key_id()
				record [-2] = zi
				record [-1] = freq
				self.db.execute (sqlstr % database, record)
			except Exception:
				print count, ': ', zi.encode('utf8'), ' ', pinyin
				import traceback
				traceback.print_exc()
			count += 1

		self.db.commit()	
	
	def optimize_database (self, database='main'):
		sqlstr = '''
			CREATE TABLE tmp AS SELECT * FROM %(database)s.phrases;
			DELETE FROM %(database)s.phrases;
			INSERT INTO %(database)s.phrases SELECT * FROM tmp ORDER BY %(xmstr)s mlen ASC, freq DESC;
			DROP TABLE tmp;
			CREATE TABLE tmp AS SELECT * FROM %(database)s.goucima;
			DELETE FROM %(database)s.goucima;
			INSERT INTO %(database)s.goucima SELECT * FROM tmp ORDER BY zi,g0,g1;
			DROP TABLE tmp;
			CREATE TABLE tmp AS SELECT * FROM %(database)s.pinyin;
			DELETE FROM %(database)s.pinyin;
			INSERT INTO %(database)s.pinyin SELECT * FROM tmp ORDER BY p0,p1,p2,p3,p4,p5,plen ASC;
			DROP TABLE tmp;
			'''
		xmstr = ''
		for i in range(self._mlen):
			xmstr +='m%d, ' % i
		self.db.executescript (sqlstr % {'database':database,'xmstr':xmstr })
		self.db.executescript ("VACUUM;")
		self.db.commit()
	
	def create_indexes(self, database):
		sqlstr = '''
			DROP INDEX IF EXISTS %(database)s.goucima_index_z;
			CREATE INDEX IF NOT EXISTS %(database)s.goucima_index_z ON goucima (zi,g0,g1);
			DROP INDEX IF EXISTS %(database)s.pinyin_index_i;
			CREATE INDEX IF NOT EXISTS %(database)s.pinyin_index_i ON pinyin (p0,p1,p2,p3,p4,p5,plen ASC, freq DESC);
			VACUUM; 
			''' % { 'database':database }

		sqlstr_t = '''
			DROP INDEX IF EXISTS %(database)s.phrases_index_p;
			CREATE INDEX IF NOT EXISTS %(database)s.phrases_index_p ON phrases (%(xmstr)s mlen ASC, freq DESC);
			DROP INDEX IF EXISTS %(database)s.phrases_index_i;
			CREATE INDEX IF NOT EXISTS %(database)s.phrases_index_i ON phrases (phrase, mlen ASC);
			''' 
		xmstr = ''
		for i in range(self._mlen):
			xmstr +='m%d,' % i
		if database == 'main':
			sqlstr = sqlstr_t % {'database':database,'xmstr':xmstr } + sqlstr
		else:
			sqlstr = sqlstr_t % {'database':database,'xmstr':xmstr }
		self.db.executescript (sqlstr)
		self.db.commit()
	
	def compare (self,x,y):
		return cmp (x[0],y[0]) or -(cmp (x[-1],y[-1])) or -(cmp (x[-2],y[-2]))

	def select_words( self, xms ):
		'''
		Get phrases from database by XingMa_Key objects
		( which should be equal or less than the max key length)
		This method is called in XingMa by passing UserInput held data
		Return result[:] 
		'''
		# firstly, we make sure the len we used is equal or less than the max key length
		_len = min( len(xms),self._mlen )
		_condition = ''
		for i in range(_len):
			_condition += 'AND m%d = ? ' % i
		# you can increase the x in _len + x to include more result, but in the most case, we only need one more key result, so we don't need the extra overhead :)
		# if len == 1 the x will be 2, if len > 1, the x will be 3
		if _len > 1:
			x_len = 3
		else:
			x_len = 2
		sqlstr = '''SELECT * FROM (SELECT * FROM main.phrases WHERE mlen < %(mk)d  %(condition)s 
		UNION ALL
		SELECT * FROM user_db.phrases WHERE mlen < %(mk)d %(condition)s )
		ORDER BY mlen ASC, user_freq DESC, freq DESC;''' % { 'mk':_len+x_len, 'condition':_condition}
		# we have redefine the __int__(self) in class XMDict.XingMa_key to return the key id, so we can use map to got key id :)
		_xms = map(int,xms[:_len])
		_xms += _xms
		result = self.db.execute(sqlstr, _xms).fetchall()
		self.db.commit()
		sysdb={}
		usrdb={}
		_cand = []
		for _res in result:
			if _res[-2]:
				sysdb[ _res[:-2] ] = [_res[:-1],_res[-1:]]
			else:
				usrdb[ _res[:-2] ] = [_res[:-1],_res[-1:]]
			# here we use [-1:] to get a tuple
		for key in usrdb:
			if not sysdb.has_key (key):
				_cand.append( usrdb[key][0] + usrdb[key][1] )
			else:
				_cand.append( sysdb[key][0] + usrdb[key][1] )
		for key in sysdb:
			if not usrdb.has_key (key):
				_cand.append( sysdb[key][0] + sysdb[key][1] )
		
		_cand.sort(cmp=self.compare)
		
		return _cand[:]

	def select_zi( self, xms ):
		'''
		Get zi from database by XingMa_Key objects
		( which should be equal or less than 6)
		This method is called in XingMa by passing UserInput held data
		Return  result[:] 
		'''
		# firstly, we make sure the len we used is equal or less than the max pinyin length 6
		_len = min( len(xms), 6 )
		_condition = ''
		for i in range(_len):
			_condition += 'AND p%d = ? ' % i
		# you can increase the x in _len + x to include more result, but in the most case, we only need one more key result, so we don't need the extra overhead :)
		sqlstr = '''SELECT * FROM main.pinyin WHERE plen < %(mk)d  %(condition)s 
		ORDER BY plen ASC, freq DESC;''' % { 'mk':_len+2, 'condition':_condition}
		# we have redefine the __int__(self) in class XMDict.XingMa_key to return the key id, so we can use map to got key id :)
		_xms = map(int,xms[:_len])
		result = self.db.execute(sqlstr, _xms).fetchall()
		self.db.commit()
		return result[:]

	def get_ime_property( self, attr ):
		'''get IME property from database, attr is the string of property,
		which should be str.lower() :)
		'''
		sqlstr = 'SELECT val FROM main.ime WHERE attr = ?' 
		_result = self.db.execute( sqlstr, (attr,)).fetchall()
		self.db.commit()
		if _result:
			return _result[0][0]
		else:
			return None

	def get_phrase_table_index (self):
		'''get a list of phrase table columns name'''
		return self._pt_index[:]

	def generate_userdb_desc (self):
		try:
			sqlstring = 'CREATE TABLE IF NOT EXISTS user_db.desc (name PRIMARY KEY, value);'
			self.db.executescript (sqlstring)
			sqlstring = 'INSERT OR IGNORE INTO user_db.desc  VALUES (?, ?);'
			self.db.execute (sqlstring, ('version', '0.1'))
			self.db.execute (sqlstring, ('id', str(uuid.uuid4 ())))
			sqlstring = 'INSERT OR IGNORE INTO user_db.desc  VALUES (?, DATETIME("now", "localtime"));'
			self.db.execute (sqlstring, ("create-time", ))
			self.db.commit ()
		except:
			import traceback
			traceback.print_exc ()

	def init_user_db (self,db_file):
		if not path.exists (db_file):
			db = sqlite3.connect (db_file)
			db.execute('PRAGMA page_size = 4096;')
			db.execute( 'PRAGMA default_cache_size = 5000;' )
			db.commit()
	
	def get_database_desc (self, db_file):
		if not path.exists (db_file):
			return None
		try:
			db = sqlite3.connect (db_file)
			desc = {}
			for row in db.execute ("SELECT * FROM desc;").fetchall():
				desc [row[0]] = row[1]
			self.db.commit()
			return desc
		except:
			return None
	
	def get_gcm_id (self, zi):
		'''Get goucima of given character'''
		sqlstr = 'SELECT g0,g1 FROM main.goucima WHERE zi =?;'
		return self.db.execute(sqlstr,(zi,)).fetchall()[0]

	def parse_phrase (self, phrase):
		'''Parse phrase to get its XingMa code'''
		# first we make sure that we are parsing unicode string
		try:
			phrase = unicode(phrase)
		except:
			phrase = phrase.decode('utf8')
		p_len = len(phrase) 
		xmlist = []
		if p_len < 2:
			# phrase should not be shorter than 2
			return []
		try:
			if p_len >= self.rules['above']:
				rule = self.rules[ self.rules['above'] ]
			elif p_len in self.rules:
				rule = self.rules[p_len]
			else:
				raise Exception ('unsupport len of phrase')
			if len(rule) > self._mlen:
				raise Exception ('fault rule: %s' % rule)
			for (zi,ma) in rule:
				if zi > 0:
					zi -= 1
				gcm = self.get_gcm_id (phrase[zi])
				xmlist.append(gcm[ma-1])
			return [len( xmlist)] + [p_len]  + xmlist[:] + [phrase]

		except Exception:
			import traceback
			traceback.print_exc ()

	def parse_phrase_to_xm (self,phrase):
		'''Get the XingMa encoding of the phrase in string form'''
		xmres = self.parse_phrase (phrase) [2:-1]
		xms = u''
		for xm in xmres:
			xms += self.deparse (xm)
		return xms

	def check_phrase (self,phrase):
		# if IME didn't support user define phrase,
		# we divide user input phrase into characters,
		# and then check its frequence
		_phrase = phrase.decode('utf8')
		if self.user_can_define_phrase:
			self.check_phrase_internal (_phrase)
		else:
			for _char in phrase:
				self.check_phrase_internal (_char)
	
	def check_phrase_internal (self,phrase):
		'''Check word freq and user_freq
		'''
		phrase = phrase.decode('utf8')
		if len(phrase) >=2:
			wordattr = self.parse_phrase ( phrase )
			_len = len (wordattr) -3
		sqlstr = '''SELECT * FROM (SELECT * FROM main.phrases WHERE phrase = ?
		UNION ALL SELECT * FROM user_db.phrases WHERE phrase = ?) ORDER BY user_freq DESC, freq DESC
''' 
		
		result = self.db.execute(sqlstr, (phrase,phrase)).fetchall()
		sysdb = {}
		usrdb = {}
		for _res in result:
			if _res[-2] > 0:
				sysdb[ _res[:-2] ] = int( _res[-1] )
			else:
				usrdb[ _res[:-2] ] =[int (_res[-2]), int( _res[-1] )]
		xmkey = ''
		if len(phrase) >=2:
			for k in wordattr[2:2+_len]:
				xmkey += self.deparse (k)
		
		sqlstr = 'UPDATE user_db.phrases SET user_freq = ? WHERE mlen = ? AND clen = ? %s AND phrase = ?;'
		
		try:
			if len(phrase) == 1:
				# this is a character
				for res in sysdb.keys():
					if usrdb.has_key(res):
						# this character is already in user_db
						_con = [ usrdb[res][1] + 1 ] 
						for r in res:
							_con.append(r)
						_condition = ''	
						for i in range(res[0]):
							_condition += 'AND m%d = ? ' % i
						nn = _con.count(None)
						if nn:
							for i in range(nn):
								_con.remove(None)
						self.db.execute ( sqlstr % _condition, _con )
					else:
						# we need to add this character into user_db
						xmkey=''
						for k in res[2:2+int(res[0])]:
							xmkey += self.deparse (k)
						self.add_phrases ([(xmkey,phrase,0,1)], database = 'user_db')
			else:
				# this is a phrase
				if len (result) == 0 and self.user_can_define_phrase:
					# this is a new phrase, we add it into user_db
					self.add_phrases ([(xmkey,phrase,-1,1)], database = 'user_db')
				elif len (result) > 0:
					for res in sysdb.keys():
						if usrdb.has_key(res):
							# this phrase is already in user_db
							_con = [ usrdb[res][1] + 1 ] 
							if usrdb[res][0] == -1:
								# this is phrase already enter sysdb
								_sqlstr = 'UPDATE user_db.phrases SET user_freq = ?, freq = ? WHERE mlen = ? AND clen = ? %s AND phrase = ?;'
								_con.append(0)
							for r in res:
								_con.append(r)
							_condition = ''	
							for i in range(res[0]):
								_condition += 'AND m%d = ? ' % i
							nn = _con.count(None)
							if nn:
								for i in range(nn):
									_con.remove(None)
							if usrdb[res][0] == -1:
								self.db.execute ( _sqlstr % _condition, _con )
							else:
								self.db.execute ( sqlstr % _condition, _con )
						else:
							# this phrase is not in user_db
							xmkey=''
							for k in res[2:2+res[0]]:
								xmkey += self.deparse (k)
							self.add_phrases ([(xmkey,phrase,0,1)], database = 'user_db')
					for res in usrdb.keys():
						if sysdb.has_key(res):
							# we have processed it above
							pass
						else:
							# this is a user added phrase
							_con = [ usrdb[res][1] + 1 ] 
							for r in res:
								_con.append(r)
							_condition = ''	
							for i in range(res[0]):
								_condition += 'AND m%d = ? ' % i
							nn = _con.count(None)
							if nn:
								for i in range(nn):
									_con.remove(None)
							self.db.execute ( sqlstr % _condition, _con )

				else:
					# we come to here when the ime dosen't support user phrase define
					pass
			
			self.db.commit()
		except:
			import traceback
			traceback.print_exc ()

	def find_zi_code (self,zi):
		'''Check word freq and user_freq
		'''
		zi = zi.decode('utf8')
		sqlstr = '''SELECT * FROM main.phrases WHERE phrase = ?
		ORDER BY mlen ASC;
''' 
		result = self.db.execute(sqlstr, (zi,)).fetchall()
		self.db.commit()
		codes = []
		try:
			if result:
				for _res in result:
					xmkey = u''
					for i in range ( int ( _res[0] ) ):
						xmkey += self.deparse ( _res[2+i] )
					codes.append(xmkey)
		except:
			import traceback
			traceback.print_exc ()
		return codes[:]

	def remove_phrase (self,phrase,database='user_db'):
		'''Remove phrase from database, default is from user_db
		phrase should be the a row of select * result from database
		Like (mlen,clen,m0,m1,m2,m3,phrase,freq,user_freq)
		'''
		_ph = []
		for attr in phrase:
			_ph.append(attr)
		_condition = ''	
		for i in range(_ph[0]):
			_condition += 'AND m%d = ? ' % i
		nn =_ph.count(None)
		if nn:
			for i in range(nn):
				_ph.remove(None)
		sqlstr = 'DELETE FROM %(database)s.phrases WHERE mlen = ? AND clen =? %(condition)s AND phrase = ? AND freq = ? AND user_freq = ?' % { 'database':database, 'condition':_condition }
		self.db.execute(sqlstr,_ph)
